<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Updating Complex Data with a DataSet and Stored Procedures</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Updating Complex Data with a DataSet and Stored Procedures</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div>
<div>UPDATE command: <b><span id="outUPdate" runat="server"></span></b></div>
<div>INSERT command: <b><span id="outInsert" runat="server"></span></b></div>
<div>DELETE command: <b><span id="outDelete" runat="server"></span></b></div>

<div id="outError" runat="server">&nbsp;</div>

<b>Initial contents of the Books table:</b>
<asp:datagrid id="dgrResult1" runat="server" /><p />
<b>Contents of the Books table after editing:</b>
<asp:datagrid id="dgrResult2" runat="server" /><p />

<b>Note:</b> while this page does execute the Update method of the DataSet, it does not<br />
actually change the original data. It uses a transaction that is rolled back afterwards.</br />
Otherwise, the update would prevent the code from running the next time.

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT BookList.*, BookAuthors.FirstName, BookAuthors.LastName " _
             & "FROM BookList INNER JOIN BookAuthors ON BookList.ISBN = BookAuthors.ISBN " _
             & "WHERE BookList.ISBN LIKE '18610033%'"
   outSelect.innerText = strSelect   'and display it

   'create a new DataSet object
   Dim objDataSet As New DataSet()

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(strConnect)

   'create a new DataAdapter using the connection object and select statement
   Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

   Try

      'fill the dataset with data from the DataAdapter object
      objDataAdapter.Fill(objDataSet, "Books")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'accept the changes to "fix" the current state of the DataSet contents
   objDataSet.AcceptChanges()

   'declare a variable to reference the Books table
   Dim objTable As DataTable = objDataSet.Tables("Books")

   'display the contents of the Books table before changing data
   dgrResult1.DataSource = objTable.DefaultView
   dgrResult1.DataBind()  'and bind (display) the data

   'now change some records in the Books table
   objTable.Rows(0).Delete()
   objTable.Rows(1)("Title") = "Amateur Theatricals for Windows 2000"
   objTable.Rows(1)("PublicationDate") = "01-01-2003"
   objTable.Rows(1)("FirstName") = "Priscilla"
   objTable.Rows(1)("LastName") = "Prince"
   objTable.Rows(2).Delete()

   'add a new row using an array of values
   Dim objValsArray(4) As Object
   objValsArray(0) = "200000001"
   objValsArray(1) = "Impressionist Guide to Painting Computers"
   objValsArray(2) = "05-02-2002"
   objValsArray(3) = "Mark"
   objValsArray(4) = "Lard"
   objTable.Rows.Add(objValsArray)

   'display the contents of the Books table after changing the data
   dgrResult2.DataSource = objTable.DefaultView
   dgrResult2.DataBind()  'and bind (display) the data

   'now have to create Command objects for the DataSetCommand to
   'use to update the original data. First the Update command
   Dim objUpdateCommand As New OleDbCommand("BookAuthorUpdate", objConnect)
   objUpdateCommand.CommandType = CommandType.StoredProcedure

   'now create the Parameter objects and add to the Command object
   Dim objParam As OleDbParameter
   objParam = objUpdateCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "ISBN"
   objParam.SourceVersion = DataRowVersion.Original   'used in SQL WHERE clause
   objParam = objUpdateCommand.Parameters.Add("Title", OleDbType.VarChar, 100)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "Title"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objUpdateCommand.Parameters.Add("PublicationDate", OleDbType.DBDate)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "PublicationDate"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objUpdateCommand.Parameters.Add("FirstName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "FirstName"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objUpdateCommand.Parameters.Add("LastName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "LastName"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause

   'now specify this Command object as the UpdateCommand
   objDataAdapter.UpdateCommand = objUpdateCommand

   'next, the InsertCommand
   Dim objInsertCommand As New OleDbCommand("BookAuthorInsert", objConnect)
   objInsertCommand.CommandType = CommandType.StoredProcedure
   objParam = objInsertCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "ISBN"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objInsertCommand.Parameters.Add("Title", OleDbType.VarChar, 100)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "Title"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objInsertCommand.Parameters.Add("PublicationDate", OleDbType.DBDate)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "PublicationDate"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objInsertCommand.Parameters.Add("FirstName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "FirstName"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objParam = objInsertCommand.Parameters.Add("LastName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "LastName"
   objParam.SourceVersion = DataRowVersion.Current   'used in SQL SET clause
   objDataAdapter.InsertCommand = objInsertCommand

   'next, the DeleteCommand
   Dim objDeleteCommand As New OleDbCommand("BookAuthorDelete", objConnect)
   objDeleteCommand.CommandType = CommandType.StoredProcedure
   objParam = objDeleteCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "ISBN"
   objParam.SourceVersion = DataRowVersion.Original   'used in SQL WHERE clause
   objParam = objDeleteCommand.Parameters.Add("FirstName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "FirstName"
   objParam.SourceVersion = DataRowVersion.Original   'used in SQL WHERE clause
   objParam = objDeleteCommand.Parameters.Add("LastName", OleDbType.VarChar, 50)
   objParam.Direction = ParameterDirection.Input
   objParam.SourceColumn = "LastName"
   objParam.SourceVersion = DataRowVersion.Original   'used in SQL WHERE clause
   objDataAdapter.DeleteCommand = objDeleteCommand

   'display the SQL statements that we created for the DataSet to use
   Dim strSQL As String  'create a new string to store vales

   'get stored proc name and source column names for each parameter
   strSQL = objDataAdapter.UpdateCommand.CommandText
   For Each objParam In objDataAdapter.UpdateCommand.Parameters
      strSQL += " @" & objParam.SourceColumn & ","
   Next
   strSQL = Left(strSQL, Len(strSQL) - 1)   'remove trailing comma
   outUpdate.InnerText = strSQL   'and display it

   'repeat the process for the Insert command
   strSQL = objDataAdapter.InsertCommand.CommandText
   For Each objParam In objDataAdapter.InsertCommand.Parameters
      strSQL += " @" & objParam.SourceColumn & ","
   Next
   strSQL = Left(strSQL, Len(strSQL) - 1)   'remove trailing comma
   outInsert.InnerText = strSQL

   'repeat the process for the Delete command
   strSQL = objDataAdapter.DeleteCommand.CommandText
   For Each objParam In objDataAdapter.DeleteCommand.Parameters
      strSQL += " @" & objParam.SourceColumn & ","
   Next
   strSQL = Left(strSQL, Len(strSQL) - 1)   'remove trailing comma
   outDelete.InnerText = strSQL

   'declare a variable to hold a Transaction object
   Dim objTransaction As OleDbTransaction

   Try

      'now we're finally ready to do the update itself
      'start a transaction so that we can roll back the changes
      'must do this on an open Connection object
      objConnect.Open()
      objTransaction = objConnect.BeginTransaction()

      'perform the update on the original data
      objInsertCommand.Transaction = objTransaction
      objUpdateCommand.Transaction = objTransaction
      objDeleteCommand.Transaction = objTransaction
      objDataAdapter.Update(objDataSet, "Books")

      'to actually update the source tables just change the next line
      'to CommitTransaction() or remove the transaction altogether.
      'note that you will then have to change the SELECT statement and
      'code to be able to run the example again, or rebuild the
      'original data from the supplied SQL scripts
      objTransaction.Rollback()

   Catch objError As Exception

      objTransaction.Rollback()
      'display error details
      outError.innerHTML = "<b>* Error while updating original data source</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

End Sub
</script>

<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
